package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import Model.Court;


public class DBCourt implements IFDBCourt {
	private static Connection con;
	Statement stmt;
	public DBCourt() 
	{
		con = DBCon.getInstance().getDBCon();
	}

	@Override
	public void createCourt(String co_name)
	{
		try{
			int coID=GetMax.getMaxId("Select max(coID) from Court")+1;//Gets the maximum coID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Court(coID, co_name )  VALUES('"+coID+"','"+co_name+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDCourt#deleteCourt(int)
	 */
	@Override
	public void deleteCourt(int coID)
	{	//coID=1; 
		try{
			String query="DELETE FROM Court WHERE coID = '"+coID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	/* Updating the Court has only one column that is updatable
	 * Henceforth 'switch' is not needed here
	 */
	@Override
	public void updateCourt(Court g)
	{ Court suObj = g;
	String query="UPDATE Court SET "+
		 	  "co_name ='"+ suObj.getCo_name()+"', "+
		          " WHERE coID = '"+ suObj.getCoID() + "'";
              System.out.println("Update query:" + query);
              try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
		
	}
	
	private String buildQuery()
	{
	    String query="SELECT coID, co_name  FROM Court";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	
	
	@Override
	public ArrayList<Court> miscWhere()
	{
            ResultSet results;
	    ArrayList<Court> list = new ArrayList<Court>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Court coObj = new Court();
		         coObj = buildCourt(results);	
                 list.add(coObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	
	@Override
	public Court findCourt(int coID)
	{
		ResultSet results;
		Court coObj = new Court();
                
	        String query =  buildQuery() + " WHERE coID ="+"'"+coID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            coObj = buildCourt(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Court was found
                            coObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return coObj;
	}
	private Court buildCourt(ResultSet results)
    {   Court suObj = new Court();
        try{ // the columns from the table glayee  are used
              suObj.setCoID(results.getInt("coID"));
              suObj.setCo_name(results.getString("co_name"));

              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Court object");
       }
       return suObj;
}
	
}
